using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using DTO;
using System.Data;
namespace DAOLayer
{
    public class MuonDAO:AbStractDao
    {
        public MuonDAO()
        {
            this.connect();
        }
        
        public static DataTable getListMuon()
        {
            string query = "SELECT * FROM Muon";
            MuonDAO dgDao = new MuonDAO();
            DataTable dt = dgDao.fillDataTable(query);
            dgDao.disconnect();           
            return dt;
        }

        public static DataTable getListQuaHan(DateTime ngay)
        {
            string query = "SELECT * FROM Muon WHERE #" + ngay + "# > ngayHetHan" ;

            //string query = "SELECT * FROM Muon WHERE tienPhat = 5000";

            //string query = "SELECT * FROM Muon WHERE ngayHetHan = #2009/04/29#";

            MuonDAO muonDAO = new MuonDAO();
            DataTable dt = muonDAO.fillDataTable(query); 
            muonDAO.disconnect();
            return dt;
        }

        public static DataTable getMaMuonSach(int maDocGia)
        {
            string query = "SELECT * FROM Muon WHERE maDocGia = " + maDocGia + " and tinhTrang = true";
            MuonDAO dgDao = new MuonDAO();
            DataTable dt = dgDao.fillDataTable(query);
            dgDao.disconnect();
            return dt;
        }

        /*public static DataTable getThongTinDocGia(int maDocGia)
        {
            string query = "select m.maDocGia,dg.hoTen,dg.ngaySinh,dg.dienThoai,dg.hanSuDung,dg.maLop,dg.maHocSinh";
            query += " from Muon m,DocGia dg";
            query += " where m.maDocGia = " + maDocGia;
            query += " and m.maDocGia = dg.maDocGia";
            MuonDAO muonDAO = new MuonDAO();
            DataTable dt = muonDAO.fillDataTable(query);
            muonDAO.disconnect();

            return dt;
        }*/

        public static Boolean kiemTraMuonSach(int maDocGia)
        {
            string strQuery = "select * from Muon where maDocGia = " + maDocGia + " and tinhTrang = true";
            MuonDAO dgDao = new MuonDAO();
            IDataReader dr = dgDao.executeQuery(strQuery);
            Boolean flag = false;
            if (dr.Read())
                flag = true;    // chua tra
            dgDao.disconnect();
            return flag;
        }
        public static MuonDTO getMuon(int maMuon)
        {
            string strQuery = "select * from Muon where maMuonSach= " + maMuon;
            MuonDAO dgDao = new MuonDAO();
            IDataReader dr = dgDao.executeQuery(strQuery);
            MuonDTO dgDto = new MuonDTO();
            if (dr.Read())
            {
                dgDto.MaMuonSach = dr.GetInt32(0);
                dgDto.MaDocGia = dr.GetInt32(1);
                dgDto.NgayMuon = dr.GetDateTime(2);
                dgDto.NgayHetHan = dr.GetDateTime(3);
                dgDto.NgayTra = dr.GetDateTime(4);
                dgDto.TienPhat = dr.GetInt32(5);
                dgDto.GhiChu = dr.GetString(6);
                dgDto.TinhTrang = dr.GetBoolean(7);
            }
            dgDao.disconnect();
            return dgDto;
        }

        public static MuonDTO getMuonTheoMaDocGia(int maDocGia)
        {
            string strQuery = "select * from Muon where maDocGia = " + maDocGia;
            MuonDAO dgDao = new MuonDAO();
            IDataReader dr = dgDao.executeQuery(strQuery);
            MuonDTO dgDto = new MuonDTO();
            if (dr.Read())
            {
                dgDto.MaMuonSach = dr.GetInt32(0);
                dgDto.MaDocGia = dr.GetInt32(1);
                dgDto.NgayMuon = dr.GetDateTime(2);
                dgDto.NgayHetHan = dr.GetDateTime(3);
                dgDto.NgayTra = dr.GetDateTime(4);
                dgDto.TienPhat = dr.GetInt32(5);
                dgDto.GhiChu = dr.GetString(6);
                dgDto.TinhTrang = dr.GetBoolean(7);
            }
            dgDao.disconnect();
            return dgDto;
        }

        public bool insert(ref MuonDTO info)
        {

            string insertCommand = "INSERT INTO Muon(MaDocGia,NgayMuon,NgayHetHan,NgayTra,TienPhat,GhiChu,TinhTrang) VALUES(" +
                info.MaDocGia + ", '" +
                info.NgayMuon + "', '" +
                info.NgayHetHan + "', '" +
                info.NgayTra + "', " +
                info.TienPhat + ", '" +
                info.GhiChu + "', " +
                info.TinhTrang + ")";
            bool flag = executeNonQuery(insertCommand);     
            return flag;
        }
        public bool update(MuonDTO info)
        {
            string updateCommand = "UPDATE Muon " +
                                    "SET ngayTra = '" + info.NgayTra +  "'" +                                
                                    " WHERE MaMuon = " + info.MaMuonSach ;
            bool flag = executeNonQuery(updateCommand);
    
            return flag;
        }

        public bool updateTinhTrang(int maDocGia, DateTime ngayTra, int tienPhat)
        {
            string updateCommand = "UPDATE Muon " +
                                    "SET tinhTrang = " + false  + " ,ngayTra = '" + ngayTra + "' ,tienPhat = " + tienPhat +
                                    " WHERE maDocGia = " + maDocGia;
            bool flag = executeNonQuery(updateCommand);

            return flag;
        }

        public bool updateNgayHetHan(int maDocGia, DateTime ngayGiaHan)
        {
            string updateCommand = "UPDATE Muon " +
                                    "SET ngayHetHan = '" + ngayGiaHan +
                                    "' WHERE maDocGia = " + maDocGia;
            bool flag = executeNonQuery(updateCommand);

            return flag;
        }

        public bool delete(int maMuon)
        {
            string deleteCommand = "DELETE FROM Muon WHERE MaMuon = " + maMuon;
            bool flag = this.executeNonQuery(deleteCommand);
            return flag;
        }

        // An them vao phan sau de lam phan bao cao thong ke

        public float getTyLeMuonQuaHan(DateTime ngay)
        {
            string strquery = "SELECT count(*) FROM Muon";
            string query = "SELECT count(*) FROM Muon WHERE #" + ngay + "# > ngayHetHan";

            long TongSo = 0;
            long SoSach = 0;

            MuonDAO muonDAO = new MuonDAO();
            IDataReader dr = muonDAO.executeQuery(query);
            if (dr.Read())
                SoSach = dr.GetInt32(0);

            IDataReader dr2 = muonDAO.executeQuery(strquery);
            if (dr2.Read())
                TongSo = dr2.GetInt32(0);

            float TyLe = 0;
            if (TyLe != 0)
                TyLe = SoSach / TongSo * 100;

            muonDAO.disconnect();
            return TyLe;
        }

        public long getThongKeNam()
        {
            long soluotmuon = 0;

            string year = DateTime.Today.Year.ToString();
            string strQuery = "select count(*) from Muon where year(NgayMuon) = " + year;
            MuonDAO dgDao = new MuonDAO();
            IDataReader dr = dgDao.executeQuery(strQuery);
            if (dr.Read())
                soluotmuon = dr.GetInt32(0);

            dgDao.disconnect();
            return soluotmuon;
        }

        public float getThoiGianMuonSach()
        {
            string strquery = "SELECT count(*) FROM Muon";
            //string Query = "SELECT datepart(hour, NgayMuon) FROM Muon";
            string Query = "SELECT sum(datediff(dd, NgayMuon, NgayTra)) FROM Muon";

            long TongSo = 0;
            long SoNgay = 0;

            MuonDAO muonDAO = new MuonDAO();

            IDataReader dr2 = muonDAO.executeQuery(strquery);
            if (dr2.Read())
                TongSo = dr2.GetInt32(0);

            IDataReader dr = muonDAO.executeQuery(Query);
            if (dr != null)
            {
                if (dr.Read())
                    SoNgay = dr.GetInt32(0);

                float TrungBinh = 0;
                if (TongSo != 0)
                    TrungBinh = SoNgay / TongSo;
                muonDAO.disconnect();
                return TrungBinh;
            }
            else return 0;
        }

        public long getThongKeGio()
        {
            long giomuon = 0;

            string strQuery = "SELECT count(datepart(hour, NgayMuon)), datepart(hour, NgayMuon) FROM Muon GROUP BY datepart(hour, NgayMuon) ORDER BY count(datepart(hour, NgayMuon)) DESC";
            MuonDAO dgDao = new MuonDAO();
            IDataReader dr = dgDao.executeQuery(strQuery);
            if (dr != null)
            {
                if (dr.Read())
                    giomuon = dr.GetInt32(1);

                dgDao.disconnect();
                return giomuon;
            }
            else return 0;
        }
        public DataTable getListMuonBaoCao(string thang, string nam)
        {
            string query = "SELECT * FROM Muon Where month(NgayMuon) = " + thang + " and year(NgayMuon) = " + nam;
            MuonDAO dgDao = new MuonDAO();
            DataTable dt = dgDao.fillDataTable(query);
            dgDao.disconnect();
            return dt;
        }
    }
}
